var mysql = require("mysql")
var express = require("express")
var app = express()
var cors = require('cors');
const bodyParser = require('body-parser')
app.use(bodyParser.json())
app.use(cors());
var client = mysql.createConnection({
    host: 'localhost',
    port: "3306",
    user: 'root',
    password: '267870',
    database: 'library'
})
client.connect(function (err) {
    if (err) throw err
    console.log("连接数据库成功")
})
app.listen(5000, function () {
    console.log("server start:  http://127.0.0.1:5000/")
})

// 查询所有账单
app.post('/bill/allBill', function (req, res) {
    // 查询全部
    let data = req.body
    client.query("select * from jzapp where userId=?", [data.userId], function (err, results) {
        if (err) throw err
        results = results.map(item => {
            item.type = item.type.split('-')
            return item
        })
        return res.send({
            data: results,
            msg: 'success'
        })
    })
})
// 删除账单
app.post('/bill/deleteBill', function (req, res) {
    client.query("delete from jzapp where id=?", [req.body.id], function (err, results) {
        console.log("删除记录个数为：" + results.affectedRows)
    })
})
// 更新账单
app.post('/bill/updateBill', function (req, res) {
    let data = req.body
    data.type = data.type.join('-')
    data.time = data.time.replace('年', '-')
    data.time = data.time.replace('月', '-')
    data.time = data.time.replace('日', '-')
    data.time = data.time.split('-')
    data.time = data.time[0] + '-' + data.time[1] + '-' + data.time[2] + ' 00:00:00'

    client.query("update  jzapp set  time=? ,money=? ,type=?,billType=? ,remark=?, shopName=? where id=?",
        [data.time, data.money, data.type, data.billType, data.remark, data.shopName, data.id, data.userId],
        function (err, results) {
            if (err) throw err
            console.log("修改记录个数为：" + results.affectedRows)
            return res.send({
                msg: 'success'
            })

        }
    )
})
// 插入账单
app.post('/bill/insertBill', function (req, res) {
    let data = req.body
    data.type = data.type.join('-')
    data.time = data.time.replace('年', '-')
    data.time = data.time.replace('月', '-')
    data.time = data.time.replace('日', '-')
    data.time = data.time.split('-')
    data.time = data.time[0] + '-' + data.time[1] + '-' + data.time[2] + ' 00:00:00'
    client.query("insert into jzapp values (null,?,?,?,?,?,null,?,?)",
        [data.time, data.money, data.type,
        data.billType, data.remark, data.shopName, data.userId],
        function (err, results) {
            if (err) throw err
            return res.send({
                msg: 'success'
            })
        })

})
// 登录
app.post('/account/login', function (req, res) {
    let data = req.body
    client.query("select * from userapp where tel=? and password=?",
        [data.tel, data.password], function (err, results) {
            if (err) throw err
            if (results.length == 0) return res.send({ msg: '账号或密码错误' })
            return res.send({
                data: JSON.stringify({ ...results[0], password: null }),
                msg: 'success'
            })
        })
})
// 验证码
app.post('/account/sendCode', function (req, res) {
    // let data = req.body
    res.send({
        data: '000000'
    })
})
// 注册账号
app.post('/account/register', function (req, res) {
    let data = req.body
    client.query("select * from userapp where tel=? ",
        [data.tel], function (err, results) {
            if (err) throw err
            if (results.length == 1) return res.send({ msg: '账号已存在，不能重复注册' })
            client.query("insert into userapp values (null,?,?,?,?,?,null)",
                [data.tel, data.password, '男', '未知', '小明'], function (err, results) {
                    if (err) throw err
                    return res.send({
                        msg: 'success'
                    })
                })
        })
})

// 更新账号
app.post('/user/updateUser', function (req, res) {
    let data = req.body
    if (data.tel != data.oldTel) {
        // 查看新电话是否被使用
        client.query("select * from userapp where tel=? ",
            [data.tel], function (err, results) {
                if (err) throw err
                if (results.length == 1) return res.send({ msg: '此电话账号已存在' })
                // 更新用户
                client.query("update  userapp set  tel=? ,sex=?,city=? ,name=? where id=?",
                    [data.tel, data.sex, data.city, data.name, data.id], function (err, results) {
                        if (err) throw err
                    })
                // 更新对应用户数据账单
                client.query("update jzapp set userId=? where userId=?",
                    [data.tel, data.oldTel],
                    function (err, results) {
                        if (err) throw err
                        // console.log('results', results.affectedRows);
                        res.send({
                            msg: 'TelSuccess'
                        })
                    }
                )

            })
    } else {
        client.query("update  userapp set sex=?,city=? ,name=? where id=?",
            [data.sex, data.city, data.name, data.id], function (err, results) {
                if (err) throw err
                return res.send({
                    msg: 'MsgSuccess',
                })
            })
    }
})

// 删除账号
app.post('/user/deleteUser', function (req, res) {
    client.query("delete from userapp where id=?", [req.body.id], function (err, results) {
        console.log("删除记录个数为：" + results.affectedRows)
        if (err) throw err
        client.query("delete from jzapp where userId=?", [req.body.oldTel], function (err, results) {
            return res.send({
                msg: 'success'
            })
        })

    })
})